﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Xml;

namespace Crystal.Common.Model
{

    /// -----------------------------------------------------------------------------
    /// <summary>
    /// Return type expected after execution result.
    /// </summary>
    /// <remarks>
    /// To specify the return type expected after execution of method.
    /// If it is mentioned DataSet then the returned object will contain dataset.
    /// </remarks>
    /// -----------------------------------------------------------------------------
    /// 

    public enum ReturnTypes
    {
        Scalar,
        NonQuery,
        DataReader,
        DataSet,
        XMLReader
    }

    /// -----------------------------------------------------------------------------
    /// Project	 : CASMain
    /// Struct	 : DataLayer.ExecutionResult
    /// 
    /// -----------------------------------------------------------------------------
    /// <summary>
    /// Structure that contains execution result of the query along with a set of parameters passed for execution.
    /// </summary>
    /// <remarks>
    /// Structure that contains execution result of the query along with a set of parameters passed for execution.
    /// Returned structure contains two objects.
    /// 1. Result Object - Containing the execution result object.
    /// 2. Result Parameters - An array of parameters that were passed for execution; to get the value of output parameters.
    /// </remarks>
    /// <history>
    /// 	[anuradhan]	11/07/2006	Created
    /// </history>
    /// -----------------------------------------------------------------------------
    /// 

    public struct ExecutionResult
    {
        public object ResultObject;
        public SqlParameter[] ResultParamenters;
        public SqlException ex;
        public string strMsg;
        public bool blnIsError;
    }

    /// -----------------------------------------------------------------------------
    /// Project	 : CASMain
    /// Class	 : DataLayer.CoreFunctions
    /// 
    /// -----------------------------------------------------------------------------
    /// <summary>
    /// Contains core functions related to database connection.
    /// </summary>
    /// <remarks>
    /// Contains core functions related to database connection.
    /// Like getting connection string, getting connection object and execution method.
    /// </remarks>
    /// <history>
    /// 	[anuradhan]	11/07/2006	Created
    /// </history>
    /// -----------------------------------------------------------------------------
    /// 

    public class CoreFunctions
    {

        //Public Shared Function GetDatabaseConnectionString() As String
        //    Return System.Configuration.ConfigurationManager.AppSettings("connectionString").Trim

        //End Function

        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Gets the connection object from the connection string specified in configuration file.
        /// </summary>
        /// <returns>An instance of connection object.</returns>
        /// <remarks>
        /// Gets the connection object from the connection string specified in configuration file.
        /// Connection needs to be opened before working on that.
        /// </remarks>
        /// <history>
        /// 	[anuradhan]	11/07/2006	Created
        /// </history>
        /// -----------------------------------------------------------------------------

        public static SqlConnection GetDatabaseConnection(string strConnectionString)
        {
            SqlConnection objConnection = null;
            try
            {
                objConnection = new SqlConnection(strConnectionString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return (objConnection);
        }

        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Common method to execute a set of queries on database configured in configuration file.
        /// </summary>
        /// <param name="CommandText">Transact-SQL statement or stored procedure to execute at the data source.</param>
        /// <param name="CommandType">Value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted</param>
        /// <param name="ReturnType">Value indicating how the result should be returned after execution</param>
        /// <param name="Parameters">Input/Output parameters required to execute the commend</param>
        /// <returns>Structure that contains execution result of the query along with a set of parameters passed for execution.</returns>
        /// <remarks>
        /// Common method to execute a set of queries on database configured in configuration file.
        /// </remarks>
        /// <history>
        /// 	[anuradhan]	11/07/2006	Created
        /// </history>
        /// -----------------------------------------------------------------------------

        public static ExecutionResult Execute(string strConnectionString, string CommandText, CommandType CommandType, ReturnTypes ReturnType, System.Data.SqlClient.SqlParameter[] Parameters = null)
        {
            // Dim objConnection As New SqlConnection(GetDatabaseConnectionString)

            dynamic objConnection = GetDatabaseConnection(strConnectionString);
            SqlCommand objCommand = new SqlCommand(CommandText, objConnection);
            ExecutionResult objExecutionResult = new ExecutionResult();
            bool blnCloseConnection = true;

            objCommand.CommandType = CommandType;
            objCommand.CommandTimeout = 0;
            //Adds Parameter To Command Object If Passed To Method
            if ((Parameters != null))
            {
                for (int i = 0; i <= Parameters.Length-1; i++)
                {
                    objCommand.Parameters.Add(Parameters[i]);
                }
            }

            try
            {
                objConnection.Open();
                switch (ReturnType)
                {
                    case ReturnTypes.NonQuery:
                        objExecutionResult.ResultObject = objCommand.ExecuteNonQuery();
                        break;
                    case ReturnTypes.Scalar:
                        objExecutionResult.ResultObject = objCommand.ExecuteScalar();
                        break;
                    case ReturnTypes.DataReader:
                        SqlDataReader objDR = null;
                        objDR = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        objExecutionResult.ResultObject = objDR;
                        blnCloseConnection = false;
                        break;
                    case ReturnTypes.DataSet:
                        SqlDataAdapter objDA = new SqlDataAdapter(objCommand);
                        DataSet objDS = new DataSet();
                        objDA.Fill(objDS);
                        objDA.Dispose();
                        objDA = null;
                        objExecutionResult.ResultObject = objDS;
                        break;
                    case ReturnTypes.XMLReader:
                        objExecutionResult.ResultObject = objCommand.ExecuteXmlReader();
                        break;
                }
                objExecutionResult.ResultParamenters = Parameters;
            }
            catch (SqlException ex)
            {
                objExecutionResult.ex = ex;
                if (!(ex.Number == 2627 || ex.Number == 547))
                    throw ex;
            }
            finally
            {
                if ((objCommand != null))
                {
                    objCommand.Dispose();
                    objCommand = null;
                }

                if (blnCloseConnection)
                {
                    if ((objConnection != null))
                    {
                        if ((objConnection.State != ConnectionState.Closed))
                        {
                            objConnection.Close();
                        }
                        objConnection.Dispose();
                        objConnection = null;
                    }
                }
            }
            return (objExecutionResult);
        }

        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Common method to execute a set of queries within a Transaction on database configured in configuration file.
        /// </summary>
        /// <param name="Transaction">Instance of a SQL-Transaction object to participate in transaction.</param>
        /// <param name="CommandText">Transact-SQL statement or stored procedure to execute at the data source.</param>
        /// <param name="CommandType">Value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted</param>
        /// <param name="ReturnType">Value indicating how the result should be returned after execution</param>
        /// <param name="Parameters">Input/Output parameters required to execute the commend</param>
        /// <returns>Structure that contains execution result of the query along with a set of parameters passed for execution.</returns>
        /// <remarks>
        /// Common method to execute a set of queries within a Transaction on database configured in configuration file.
        /// </remarks>
        /// <history>
        /// 	[anuradhan]	11/07/2006	Created
        /// </history>
        /// -----------------------------------------------------------------------------

        public static ExecutionResult Execute(SqlTransaction Transaction, string CommandText, CommandType CommandType, ReturnTypes ReturnType, System.Data.SqlClient.SqlParameter[] Parameters = null)
        {
            ExecutionResult objExecutionResult = new ExecutionResult();
            SqlCommand objCommand = null;

            objCommand = Transaction.Connection.CreateCommand();
            objCommand.Transaction = Transaction;
            objCommand.CommandText = CommandText;
            objCommand.CommandType = CommandType;
            objCommand.CommandTimeout = 0;
            //Adds Parameter To Command Object If Passed To Method
            if ((Parameters != null))
            {
                for (int i = 0; i <= Parameters.Length; i++)
                {
                    objCommand.Parameters.Add(Parameters[i]);
                }
            }

            try
            {
                switch (ReturnType)
                {
                    case ReturnTypes.NonQuery:
                        objExecutionResult.ResultObject = objCommand.ExecuteNonQuery();
                        break;
                    case ReturnTypes.Scalar:
                        objExecutionResult.ResultObject = objCommand.ExecuteScalar();
                        break;
                    case ReturnTypes.DataReader:
                        SqlDataReader objDR = null;
                        objDR = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        objExecutionResult.ResultObject = objDR;
                        break;
                    case ReturnTypes.DataSet:
                        SqlDataAdapter objDA = new SqlDataAdapter(objCommand);
                        DataSet objDS = new DataSet();
                        objDA.Fill(objDS);
                        objDA.Dispose();
                        objDA = null;
                        objExecutionResult.ResultObject = objDS;
                        break;
                    case ReturnTypes.XMLReader:
                        objExecutionResult.ResultObject = objCommand.ExecuteXmlReader();
                        break;
                }
                objExecutionResult.ResultParamenters = Parameters;
                return (objExecutionResult);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if ((objCommand != null))
                {
                    objCommand.Dispose();
                    objCommand = null;
                }
            }
        }

    }
}
